package com.fihtdc.excelwriter;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Scanner;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class ExcelWriter {
    public static final boolean     DBG                         = true;

    private final String            TAG                         = "ExcelWriter";

    private final static String     KEY_VERSION_INDEX_IN_EXCEL  = "Version";
    private final static String     KEY_FILENAME_INDEX_IN_EXCEL = "File Name";

    public static String            Project_Code;
    public static ArrayList<String> packModelList;
    public static ArrayList<String> MlfFilenameList;

    private int                     VerIndexInExcel             = -1;
    private int                     FileNameIndexInExcel        = -1;

    // Class Constructor
    public ExcelWriter(String excelLoc, String txtLoc) {
        Utility.Log(TAG + "start ===>\n");
        init(excelLoc);
        readConf(txtLoc + "/collection.conf");
        writeExcel(excelLoc, txtLoc);
    }

    public void init(String excel) {
        Project_Code = "-1";
        packModelList = new ArrayList<String>();
        MlfFilenameList = new ArrayList<String>();
    }

    public void readConf(String conf) {
        try {
            Utility.Log("read collection.conf start ===>\n");

            Scanner scanner = new Scanner(new File(conf));
            String readLine;
            // packModelList = new ArrayList<String>();
            while (scanner.hasNextLine()) {
                readLine = scanner.nextLine();
                String[] property = readLine.split(" ");
                Project_Code = property[0].split("=")[1];
                packModelList.add(property[1].split("=")[1]);
                Utility.Log("\tadd " + Project_Code + "_" + property[1].split("=")[1] + "\n");
            }
            scanner.close();

            Utility.Log("<=== read collection.conf end\n");
        } catch (FileNotFoundException e) {
            Utility.Log(TAG + ": readConf: " + conf + " not found\n");
        } catch (Exception e) {
            Utility.Log(TAG + ": readConf: " + e.getMessage());
        }
    }

    public void writeExcel(String excel, String txt) {
        int VerIndexInTxt = 5;
        int FileNameIndexInTxt = 6;

        Utility.Log("write excel start ===>\n");
        // MlfFilenameList = new ArrayList<String>();
        try {
            InputStream is = new FileInputStream(excel);
            Workbook wb = WorkbookFactory.create(is);

            // get version column index in excel
            Sheet _0001 = wb.getSheet(Project_Code + "_0001");
            boolean isVersionIndexFound = false;
            boolean isFilenameIndexFound = false;
            for (Cell cell : _0001.getRow(1)) {
                if (!isVersionIndexFound
                        && cell.getStringCellValue().equals(KEY_VERSION_INDEX_IN_EXCEL)) {
                    VerIndexInExcel = cell.getColumnIndex();
                    isVersionIndexFound = true;
                }
                if (!isFilenameIndexFound
                        && cell.getStringCellValue().equals(KEY_FILENAME_INDEX_IN_EXCEL)) {
                    FileNameIndexInExcel = cell.getColumnIndex();
                    isFilenameIndexFound = true;
                }
                if (isVersionIndexFound && isFilenameIndexFound) {
                    Utility.Log("Version column = " + VerIndexInExcel + "\nFileName column = "
                            + FileNameIndexInExcel + "\n");
                    break;
                }
            }
            if (VerIndexInExcel == -1 || FileNameIndexInExcel == -1) { throw new ArrayIndexOutOfBoundsException(
                    (VerIndexInExcel == -1) ? "Version" : "File Name"
                            + " column in excel not found"); }

            // fill version and file name into corresponding model sheet
            for (String model_code : packModelList) {
                Sheet sheet = wb.getSheet(Project_Code + "_" + model_code);
                int j = 2; // ROW-3 in index
                Scanner scanner = new Scanner(new File(txt + "/" + Project_Code + "_" + model_code
                        + ".txt"));
                while (scanner.hasNextLine()) {
                    String readLine = scanner.nextLine();
                    if (readLine.split(",")[0].equals("MLF")) {
                        MlfFilenameList.add(readLine.split(",")[1]);
                        continue;
                    }
                    String version = readLine.split(",")[VerIndexInTxt];
                    String filename = readLine.split(",")[FileNameIndexInTxt];
                    Cell verCell = (sheet.getRow(j).getCell(VerIndexInExcel) == null) ? sheet
                            .getRow(j).createCell(VerIndexInExcel) : sheet.getRow(j).getCell(
                            VerIndexInExcel);
                    verCell.setCellValue(version);
                    Cell fileNameCell = (sheet.getRow(j).getCell(FileNameIndexInExcel) == null) ? sheet
                            .getRow(j).createCell(FileNameIndexInExcel) : sheet.getRow(j).getCell(
                            FileNameIndexInExcel);
                    fileNameCell.setCellValue(filename);
                    j++;
                }
                scanner.close();
            }
            is.close();

            OutputStream os = new FileOutputStream(excel);
            wb.write(os);
            os.close();
            Utility.Log("<=== write version back to excel end\n");
        } catch (FileNotFoundException e) {
            Utility.Log(TAG + ": writeExcel: excel not found. \n" + e.getMessage());
            e.printStackTrace();
        } catch (IOException e) {
            Utility.Log(TAG + ": writeExcel: excel io error. \n" + e.getMessage());
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            Utility.Log(TAG + ": writeExcel: " + e.getMessage());
            e.printStackTrace();
        } catch (Exception e) {
            Utility.Log(TAG + ": writeExcel: " + e.getMessage());
        }
    }

    // Main
    public static void main(String[] args) {
        try {
            new ExcelWriter(args[0], args[1]);
            new MlfMaker(args[0], args[1]);
        } catch (ArrayIndexOutOfBoundsException e) {
            System.out.println("$1:excel location\n" + "$2:complete txt location");
        }
    }
}
